﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data;

namespace MyProject.DAL
{
    public class Db
    {
        private SqlConnection _connection;
        private SqlTransaction _transaction;

        public void Connect()
        {
            _connection = new SqlConnection(@"Data Source=localhost;Initial Catalog=MyProject;Integrated Security=True");
            if (_connection.State != System.Data.ConnectionState.Open)
            {
                _connection.Open();
                _transaction = _connection.BeginTransaction();
            }
        }

        public void Commit()
        {
            _transaction.Commit();
        }

        public void Rollback()
        {
            if (_transaction != null)
                _transaction.Rollback();
        }

        public void Disconnect()
        {
            _connection.Close();
            _transaction.Dispose();
        }

        public void Dispose()
        {
            this.Disconnect();
        }

        public SqlConnection getConnection()
        {
            return _connection;
        }

        public SqlTransaction getTransaction()
        {
            return _transaction;
        }

        public bool Execute(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            Debug.WriteLine(sql);

            return cmd.ExecuteNonQuery() > 0;
        }

        public object ExecuteScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            Debug.WriteLine(sql);

            return cmd.ExecuteScalar();
        }

        SqlDataReader getReader(string sql, SqlConnection connection)
        {
            var cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;

            return cmd.ExecuteReader();
        }

        public DataTable Select(string sql)
        {
            var dt = new DataTable();
            dt.Load(getReader(sql, getConnection()));
            Debug.WriteLine(sql);

            return dt;
        }
    }
}
